﻿using System;
using System.Collections.Generic;
using System.Windows.Forms;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;

namespace OfflineReport
{
    class SQL
    {
        string strConnect = ConfigurationManager.ConnectionStrings["Report.Properties.Settings.BaoCaoConnectionString"].ConnectionString;
        //string strConnect = ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString;
        SqlConnection cnn;

        public SQL() 
        {
            cnn = new SqlConnection(strConnect);
        }

        public SqlConnection open() 
        {
            try
            {
                cnn.Open();
                return cnn;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }

        public void close() 
        {
            try
            {
                cnn.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
     
        public DataTable getDataTable(string strQuery)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.open();
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = strQuery;
                
                cmd.ExecuteNonQuery();                
                SqlDataAdapter sql_Adapter = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                sql_Adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                this.close();

            }
        }

        public DataTable getDataTable(string strQuery, SqlParameter[] sqlParas) 
        {
            SqlCommand cmd = new SqlCommand();
            try
            {               
                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strQuery;
                foreach (SqlParameter sqlPara in sqlParas)
                {
                    cmd.Parameters.Add(sqlPara);
                }
                cmd.ExecuteNonQuery();               
                SqlDataAdapter sql_Adapter = new SqlDataAdapter(cmd);                
                DataTable dt = new DataTable();
                sql_Adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                cmd.Parameters.Clear();
                this.close();

            }   
        }
       
        public double getData(string LDID, string reportDate, string transType)
        {
            double value = 0d;
            string data = "";
            string sql_select = "sql_select_amount";
            SqlParameter[] pr = new SqlParameter[3];
            pr[0] = new SqlParameter("@REPORT_DATE", reportDate);
            pr[1] = new SqlParameter("@LDID", LDID);
            pr[2] = new SqlParameter("@TRANSTYPE", transType);
            DataTable dt = getDataTable(sql_select,pr);

            if (dt.Rows.Count > 0)
            {
                data = dt.Rows[0][0].ToString();
            }
            if (data != "")
            {
                value = Convert.ToDouble(data);
            }
            else value = 0;
            return value;
        }
    }
}
